-- 虚构影评网站数据库脚本 - 函数和触发器部分
-- 在Supabase Dashboard的SQL Editor中运行此脚本
-- 包含所有函数、触发器和业务逻辑

-- ========================================
-- 1. 基础触发器函数
-- ========================================

-- 更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 标签更新时间触发器函数
CREATE OR REPLACE FUNCTION update_tag_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 用户注册时自动创建资料的函数
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.user_profiles (user_id, name, avatar_url, bio)
  VALUES (
    new.id,
    COALESCE(new.raw_user_meta_data->>'name', split_part(new.email, '@', 1)),
    new.raw_user_meta_data->>'avatar_url',
    '热爱虚构电影与AI创作。'
  );
  RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 确保消息设置存在的函数
CREATE OR REPLACE FUNCTION ensure_message_settings()
RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM public.message_settings WHERE user_id = NEW.id) THEN
    INSERT INTO public.message_settings (user_id) VALUES (NEW.id);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 2. 评论系统函数
-- ========================================

-- 处理用户投票的函数
CREATE OR REPLACE FUNCTION handle_comment_vote(
    p_user_id UUID,
    p_comment_id BIGINT,
    p_vote_type SMALLINT
)
RETURNS JSON AS $$
DECLARE
    existing_vote RECORD;
    result JSON;
BEGIN
    IF p_vote_type NOT IN (1, -1) THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Invalid vote type. Must be 1 (like) or -1 (dislike)'
        );
    END IF;

    SELECT 
        user_id, comment_id, vote_type, created_at, updated_at
    INTO existing_vote 
    FROM comment_votes 
    WHERE user_id = p_user_id AND comment_id = p_comment_id;

    IF existing_vote IS NOT NULL THEN
        IF existing_vote.vote_type = p_vote_type THEN
            DELETE FROM public.comment_votes 
            WHERE user_id = p_user_id AND comment_id = p_comment_id;
            
            IF p_vote_type = 1 THEN
                UPDATE public.comments SET likes = likes - 1 WHERE id = p_comment_id;
            ELSE
                UPDATE public.comments SET dislikes = dislikes - 1 WHERE id = p_comment_id;
            END IF;
            
            RETURN json_build_object(
                'success', true,
                'action', 'removed',
                'message', 'Vote removed'
            );
        ELSE
            UPDATE public.comment_votes 
            SET vote_type = p_vote_type, updated_at = NOW()
            WHERE user_id = p_user_id AND comment_id = p_comment_id;
            
            IF existing_vote.vote_type = 1 THEN
                UPDATE public.comments SET likes = likes - 1 WHERE id = p_comment_id;
            ELSE
                UPDATE public.comments SET dislikes = dislikes - 1 WHERE id = p_comment_id;
            END IF;
            
            IF p_vote_type = 1 THEN
                UPDATE public.comments SET likes = likes + 1 WHERE id = p_comment_id;
            ELSE
                UPDATE public.comments SET dislikes = dislikes + 1 WHERE id = p_comment_id;
            END IF;
            
            RETURN json_build_object(
                'success', true,
                'action', 'updated',
                'message', 'Vote updated'
            );
        END IF;
    ELSE
        INSERT INTO public.comment_votes (user_id, comment_id, vote_type)
        VALUES (p_user_id, p_comment_id, p_vote_type);
        
        IF p_vote_type = 1 THEN
            UPDATE public.comments SET likes = likes + 1 WHERE id = p_comment_id;
        ELSE
            UPDATE public.comments SET dislikes = dislikes + 1 WHERE id = p_comment_id;
        END IF;
        
        RETURN json_build_object(
            'success', true,
            'action', 'added',
            'message', 'Vote added'
        );
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取评论及其回复的函数
CREATE OR REPLACE FUNCTION get_comments_with_replies(
    p_movie_id BIGINT,
    p_limit INTEGER DEFAULT 10,
    p_offset INTEGER DEFAULT 0,
    p_sort_by TEXT DEFAULT 'created_at'
)
RETURNS TABLE (
    comment_id BIGINT,
    user_id UUID,
    content TEXT,
    likes INTEGER,
    dislikes INTEGER,
    parent_id BIGINT,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    user_name VARCHAR,
    user_avatar TEXT,
    user_is_ai BOOLEAN,
    reply_count BIGINT,
    user_vote INTEGER
) AS $$
BEGIN
    RETURN QUERY
    WITH comment_stats AS (
        SELECT 
            c.id,
            COUNT(r.id) as reply_count
        FROM comments c
        LEFT JOIN comments r ON c.id = r.parent_id
        WHERE c.movie_id = p_movie_id AND c.parent_id IS NULL
        GROUP BY c.id
    ),
    user_votes AS (
        SELECT 
            cv.comment_id,
            cv.vote_type::INTEGER as vote_type
        FROM comment_votes cv
        WHERE cv.user_id = auth.uid()
    )
    SELECT 
        c.id as comment_id,
        c.user_id,
        c.content,
        c.likes,
        c.dislikes,
        c.parent_id,
        c.created_at,
        c.updated_at,
        up.name as user_name,
        up.avatar_url as user_avatar,
        up.is_ai as user_is_ai,
        COALESCE(cs.reply_count, 0) as reply_count,
        COALESCE(uv.vote_type, 0) as user_vote
    FROM comments c
    LEFT JOIN user_profiles up ON c.user_id = up.user_id
    LEFT JOIN comment_stats cs ON c.id = cs.id
    LEFT JOIN user_votes uv ON c.id = uv.comment_id
    WHERE c.movie_id = p_movie_id AND c.parent_id IS NULL
    ORDER BY 
        CASE 
            WHEN p_sort_by = 'likes' THEN c.likes - c.dislikes
            WHEN p_sort_by = 'created_at' THEN EXTRACT(EPOCH FROM c.created_at)
            ELSE EXTRACT(EPOCH FROM c.created_at)
        END DESC
    LIMIT p_limit OFFSET p_offset;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取单个评论的回复
CREATE OR REPLACE FUNCTION get_comment_replies(
    p_comment_id BIGINT,
    p_limit INTEGER DEFAULT 10,
    p_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
    reply_id BIGINT,
    user_id UUID,
    content TEXT,
    likes INTEGER,
    dislikes INTEGER,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    user_name VARCHAR,
    user_avatar TEXT,
    user_is_ai BOOLEAN,
    user_vote INTEGER
) AS $$
BEGIN
    RETURN QUERY
    WITH user_votes AS (
        SELECT 
            cv.comment_id,
            cv.vote_type::INTEGER as vote_type
        FROM comment_votes cv
        WHERE cv.user_id = auth.uid()
    )
    SELECT 
        c.id as reply_id,
        c.user_id,
        c.content,
        c.likes,
        c.dislikes,
        c.created_at,
        c.updated_at,
        up.name as user_name,
        up.avatar_url as user_avatar,
        up.is_ai as user_is_ai,
        COALESCE(uv.vote_type, 0) as user_vote
    FROM comments c
    LEFT JOIN user_profiles up ON c.user_id = up.user_id
    LEFT JOIN user_votes uv ON c.id = uv.comment_id
    WHERE c.parent_id = p_comment_id
    ORDER BY c.created_at ASC
    LIMIT p_limit OFFSET p_offset;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 创建回复评论的函数
CREATE OR REPLACE FUNCTION create_comment_reply(
    p_parent_id BIGINT,
    p_content TEXT
)
RETURNS JSON AS $$
DECLARE
    parent_movie_id BIGINT;
    new_reply_id BIGINT;
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();
    IF current_user_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'User not authenticated'
        );
    END IF;

    IF p_content IS NULL OR trim(p_content) = '' THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Comment content cannot be empty'
        );
    END IF;

    SELECT movie_id INTO parent_movie_id 
    FROM comments 
    WHERE id = p_parent_id;
    
    IF parent_movie_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Parent comment not found'
        );
    END IF;

    INSERT INTO public.comments (movie_id, user_id, content, parent_id)
    VALUES (parent_movie_id, current_user_id, p_content, p_parent_id)
    RETURNING id INTO new_reply_id;

    RETURN json_build_object(
        'success', true,
        'reply_id', new_reply_id,
        'message', 'Reply created successfully'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 3. 消息系统函数
-- ========================================

-- 创建消息的函数
CREATE OR REPLACE FUNCTION create_message(
    p_recipient_id UUID,
    p_sender_id UUID,
    p_type VARCHAR,
    p_title VARCHAR,
    p_content TEXT,
    p_related_movie_id BIGINT DEFAULT NULL,
    p_related_comment_id BIGINT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
    user_settings RECORD;
    message_id BIGINT;
BEGIN
    SELECT 
        user_id, comment_replies, movie_uploads, system_notifications, likes, follows
    INTO user_settings 
    FROM public.message_settings 
    WHERE user_id = p_recipient_id;
    
    IF user_settings IS NULL THEN
        INSERT INTO public.message_settings (user_id) VALUES (p_recipient_id);
        SELECT 
            user_id, comment_replies, movie_uploads, system_notifications, likes, follows
        INTO user_settings 
        FROM public.message_settings 
        WHERE user_id = p_recipient_id;
    END IF;
    
    CASE p_type
        WHEN 'comment_reply' THEN
            IF NOT user_settings.comment_replies THEN
                RETURN json_build_object('success', false, 'error', 'User has disabled comment reply notifications');
            END IF;
        WHEN 'movie_upload' THEN
            IF NOT user_settings.movie_uploads THEN
                RETURN json_build_object('success', false, 'error', 'User has disabled movie upload notifications');
            END IF;
        WHEN 'system' THEN
            IF NOT user_settings.system_notifications THEN
                RETURN json_build_object('success', false, 'error', 'User has disabled system notifications');
            END IF;
        WHEN 'like' THEN
            IF NOT user_settings.likes THEN
                RETURN json_build_object('success', false, 'error', 'User has disabled like notifications');
            END IF;
        WHEN 'follow' THEN
            IF NOT user_settings.follows THEN
                RETURN json_build_object('success', false, 'error', 'User has disabled follow notifications');
            END IF;
    END CASE;
    
    INSERT INTO public.messages (
        recipient_id, sender_id, type, title, content, 
        related_movie_id, related_comment_id
    ) VALUES (
        p_recipient_id, p_sender_id, p_type, p_title, p_content,
        p_related_movie_id, p_related_comment_id
    ) RETURNING id INTO message_id;
    
    RETURN json_build_object(
        'success', true,
        'message_id', message_id,
        'message', 'Message created successfully'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取用户消息的函数
CREATE OR REPLACE FUNCTION get_user_messages(
    p_limit INTEGER DEFAULT 20,
    p_offset INTEGER DEFAULT 0,
    p_unread_only BOOLEAN DEFAULT false
)
RETURNS TABLE (
    message_id BIGINT,
    sender_id UUID,
    sender_name VARCHAR,
    sender_avatar TEXT,
    sender_is_ai BOOLEAN,
    type VARCHAR,
    title VARCHAR,
    content TEXT,
    related_movie_id BIGINT,
    related_movie_title VARCHAR,
    related_comment_id BIGINT,
    is_read BOOLEAN,
    created_at TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        m.id as message_id,
        m.sender_id,
        up.name as sender_name,
        up.avatar_url as sender_avatar,
        up.is_ai as sender_is_ai,
        m.type,
        m.title,
        m.content,
        m.related_movie_id,
        mv.title as related_movie_title,
        m.related_comment_id,
        m.is_read,
        m.created_at
    FROM messages m
    LEFT JOIN user_profiles up ON m.sender_id = up.user_id
    LEFT JOIN movies mv ON m.related_movie_id = mv.id
    WHERE m.recipient_id = auth.uid()
    AND (NOT p_unread_only OR m.is_read = false)
    ORDER BY m.created_at DESC
    LIMIT p_limit OFFSET p_offset;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 标记消息为已读的函数
CREATE OR REPLACE FUNCTION mark_message_as_read(p_message_id BIGINT)
RETURNS JSON AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    UPDATE public.messages 
    SET is_read = true, updated_at = NOW()
    WHERE id = p_message_id AND recipient_id = auth.uid();
    
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    
    IF affected_rows > 0 THEN
        RETURN json_build_object('success', true, 'message', 'Message marked as read');
    ELSE
        RETURN json_build_object('success', false, 'error', 'Message not found or access denied');
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 标记所有消息为已读的函数
CREATE OR REPLACE FUNCTION mark_all_messages_as_read()
RETURNS JSON AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    UPDATE public.messages 
    SET is_read = true, updated_at = NOW()
    WHERE recipient_id = auth.uid() AND is_read = false;
    
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    
    RETURN json_build_object(
        'success', true, 
        'affected_rows', affected_rows,
        'message', 'All messages marked as read'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取未读消息数量的函数
CREATE OR REPLACE FUNCTION get_unread_message_count()
RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count
    FROM messages 
    WHERE recipient_id = auth.uid() AND is_read = false;
    
    RETURN count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 删除消息的函数
CREATE OR REPLACE FUNCTION delete_message(p_message_id BIGINT)
RETURNS JSON AS $$
DECLARE
    affected_rows INTEGER;
BEGIN
    DELETE FROM messages 
    WHERE id = p_message_id AND recipient_id = auth.uid();
    
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    
    IF affected_rows > 0 THEN
        RETURN json_build_object('success', true, 'message', 'Message deleted successfully');
    ELSE
        RETURN json_build_object('success', false, 'error', 'Message not found or access denied');
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 更新消息设置的函数
CREATE OR REPLACE FUNCTION update_message_settings(
    p_comment_replies BOOLEAN DEFAULT NULL,
    p_movie_uploads BOOLEAN DEFAULT NULL,
    p_system_notifications BOOLEAN DEFAULT NULL,
    p_likes BOOLEAN DEFAULT NULL,
    p_follows BOOLEAN DEFAULT NULL,
    p_email_notifications BOOLEAN DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
    user_settings RECORD;
BEGIN
    SELECT * INTO user_settings 
    FROM public.message_settings 
    WHERE user_id = auth.uid();
    
    IF user_settings IS NULL THEN
        INSERT INTO public.message_settings (user_id) VALUES (auth.uid());
        user_settings := (SELECT * FROM public.message_settings WHERE user_id = auth.uid());
    END IF;
    
    UPDATE public.message_settings 
    SET 
        comment_replies = COALESCE(p_comment_replies, comment_replies),
        movie_uploads = COALESCE(p_movie_uploads, movie_uploads),
        system_notifications = COALESCE(p_system_notifications, system_notifications),
        likes = COALESCE(p_likes, likes),
        follows = COALESCE(p_follows, follows),
        email_notifications = COALESCE(p_email_notifications, email_notifications),
        updated_at = NOW()
    WHERE user_id = auth.uid();
    
    RETURN json_build_object('success', true, 'message', 'Message settings updated successfully');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 4. 标签系统函数
-- ========================================

-- 添加电影标签
CREATE OR REPLACE FUNCTION add_movie_tag(
    p_movie_id BIGINT,
    p_tag_id BIGINT
)
RETURNS JSON AS $$
DECLARE
    movie_owner UUID;
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();
    IF current_user_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'User not authenticated'
        );
    END IF;

    SELECT uploader_id INTO movie_owner
    FROM movies
    WHERE id = p_movie_id;
    
    IF movie_owner IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Movie not found'
        );
    END IF;

    IF movie_owner != current_user_id THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Not authorized to modify this movie'
        );
    END IF;

    INSERT INTO public.movie_tags (movie_id, tag_id)
    VALUES (p_movie_id, p_tag_id)
    ON CONFLICT (movie_id, tag_id) DO NOTHING;

    RETURN json_build_object(
        'success', true,
        'message', 'Tag added successfully'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 批量添加电影标签
CREATE OR REPLACE FUNCTION add_movie_tags_batch(
    p_movie_id BIGINT,
    p_tag_ids BIGINT[]
)
RETURNS JSON AS $$
DECLARE
    movie_owner UUID;
    current_user_id UUID;
    tag_id BIGINT;
    added_count INTEGER := 0;
    error_count INTEGER := 0;
BEGIN
    current_user_id := auth.uid();
    IF current_user_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'User not authenticated'
        );
    END IF;

    SELECT uploader_id INTO movie_owner
    FROM movies
    WHERE id = p_movie_id;
    
    IF movie_owner IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Movie not found'
        );
    END IF;

    IF movie_owner != current_user_id THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Not authorized to modify this movie'
        );
    END IF;

    FOREACH tag_id IN ARRAY p_tag_ids
    LOOP
        BEGIN
            INSERT INTO public.movie_tags (movie_id, tag_id)
            VALUES (p_movie_id, tag_id)
            ON CONFLICT (movie_id, tag_id) DO NOTHING;
            
            IF FOUND THEN
                added_count := added_count + 1;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                error_count := error_count + 1;
        END;
    END LOOP;

    RETURN json_build_object(
        'success', true,
        'message', 'Batch operation completed',
        'added_count', added_count,
        'error_count', error_count
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 移除电影标签
CREATE OR REPLACE FUNCTION remove_movie_tag(
    p_movie_id BIGINT,
    p_tag_id BIGINT
)
RETURNS JSON AS $$
DECLARE
    movie_owner UUID;
    current_user_id UUID;
BEGIN
    current_user_id := auth.uid();
    IF current_user_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'User not authenticated'
        );
    END IF;

    SELECT uploader_id INTO movie_owner
    FROM movies
    WHERE id = p_movie_id;
    
    IF movie_owner IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Movie not found'
        );
    END IF;

    IF movie_owner != current_user_id THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Not authorized to modify this movie'
        );
    END IF;

    DELETE FROM movie_tags 
    WHERE movie_id = p_movie_id AND tag_id = p_tag_id;

    RETURN json_build_object(
        'success', true,
        'message', 'Tag removed successfully'
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 批量移除电影标签
CREATE OR REPLACE FUNCTION remove_movie_tags_batch(
    p_movie_id BIGINT,
    p_tag_ids BIGINT[]
)
RETURNS JSON AS $$
DECLARE
    movie_owner UUID;
    current_user_id UUID;
    removed_count INTEGER;
BEGIN
    current_user_id := auth.uid();
    IF current_user_id IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'User not authenticated'
        );
    END IF;

    SELECT uploader_id INTO movie_owner
    FROM movies
    WHERE id = p_movie_id;
    
    IF movie_owner IS NULL THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Movie not found'
        );
    END IF;

    IF movie_owner != current_user_id THEN
        RETURN json_build_object(
            'success', false,
            'error', 'Not authorized to modify this movie'
        );
    END IF;

    DELETE FROM movie_tags 
    WHERE movie_id = p_movie_id AND tag_id = ANY(p_tag_ids);

    GET DIAGNOSTICS removed_count = ROW_COUNT;

    RETURN json_build_object(
        'success', true,
        'message', 'Batch removal completed',
        'removed_count', removed_count
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取电影标签
CREATE OR REPLACE FUNCTION get_movie_tags(
    p_movie_id BIGINT
)
RETURNS TABLE (
    tag_id BIGINT,
    tag_name VARCHAR,
    tag_display_name VARCHAR,
    category_name VARCHAR,
    category_display_name VARCHAR,
    color VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.id as tag_id,
        t.name as tag_name,
        t.display_name as tag_display_name,
        tc.name as category_name,
        tc.display_name as category_display_name,
        t.color
    FROM movie_tags mt
    JOIN tags t ON mt.tag_id = t.id
    JOIN tag_categories tc ON t.category_id = tc.id
    WHERE mt.movie_id = p_movie_id AND t.is_active = true AND tc.is_active = true
    ORDER BY tc.sort_order, t.sort_order;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取电影系统标签
CREATE OR REPLACE FUNCTION get_movie_system_tags(
    p_movie_id BIGINT
)
RETURNS TABLE (
    category_name VARCHAR,
    category_display_name VARCHAR,
    tag_names VARCHAR[],
    tag_info JSON
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        tc.name as category_name,
        tc.display_name as category_display_name,
        ARRAY_AGG(t.display_name) as tag_names,
        json_agg(
            json_build_object(
                'id', t.id,
                'name', t.name,
                'display_name', t.display_name,
                'color', t.color,
                'description', t.description
            )
        ) as tag_info
    FROM movie_tags mt
    JOIN tags t ON mt.tag_id = t.id
    JOIN tag_categories tc ON t.category_id = tc.id
    WHERE mt.movie_id = p_movie_id 
      AND t.is_active = true 
      AND tc.is_active = true
    GROUP BY tc.name, tc.display_name, tc.sort_order
    ORDER BY tc.sort_order;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 按分类获取标签
CREATE OR REPLACE FUNCTION get_tags_by_category(
    category_name TEXT
)
RETURNS TABLE (
    id BIGINT,
    name VARCHAR,
    display_name VARCHAR,
    description TEXT,
    color VARCHAR,
    sort_order INTEGER,
    usage_count INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.id,
        t.name,
        t.display_name,
        t.description,
        t.color,
        t.sort_order,
        t.usage_count
    FROM tags t
    JOIN tag_categories tc ON t.category_id = tc.id
    WHERE tc.name = category_name AND t.is_active = true AND tc.is_active = true
    ORDER BY t.sort_order;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 设置电影系统标签（简化版）
CREATE OR REPLACE FUNCTION set_movie_system_tags_simple(
    p_movie_id BIGINT,
    p_genre_names TEXT[] DEFAULT NULL,
    p_universe_name TEXT DEFAULT NULL,
    p_language_name TEXT DEFAULT NULL,
    p_country_name TEXT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
    v_tag_id BIGINT;
    genre_name TEXT;
    result JSON;
    added_count INTEGER := 0;
BEGIN
    BEGIN
        -- 删除现有的系统标签关联
        DELETE FROM movie_tags mt
        WHERE mt.movie_id = p_movie_id 
        AND mt.tag_id IN (
            SELECT t.id 
            FROM tags t
            JOIN tag_categories tc ON t.category_id = tc.id 
            WHERE tc.name IN ('genre', 'universe', 'language', 'country')
        );

        -- 添加艺术类型标签
        IF p_genre_names IS NOT NULL THEN
            FOREACH genre_name IN ARRAY p_genre_names
            LOOP
                SELECT t.id INTO v_tag_id
                FROM tags t
                JOIN tag_categories tc ON t.category_id = tc.id
                WHERE tc.name = 'genre' 
                  AND t.display_name = genre_name 
                  AND t.is_active = true;
                
                IF v_tag_id IS NOT NULL THEN
                    INSERT INTO public.movie_tags (movie_id, tag_id)
                    VALUES (p_movie_id, v_tag_id)
                    ON CONFLICT (movie_id, tag_id) DO NOTHING;
                    
                    IF FOUND THEN
                        added_count := added_count + 1;
                    END IF;
                END IF;
            END LOOP;
        END IF;

        -- 添加其他系统标签
        -- (universe, language, country 的添加逻辑类似)

        result := json_build_object(
            'success', true,
            'message', 'Movie system tags updated successfully',
            'added_count', added_count
        );

    EXCEPTION
        WHEN OTHERS THEN
            result := json_build_object(
                'success', false,
                'error', 'Failed to update movie system tags: ' || SQLERRM
            );
    END;

    RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 同步标签使用计数
CREATE OR REPLACE FUNCTION sync_tag_usage_counts()
RETURNS void AS $$
BEGIN
    UPDATE public.tags 
    SET usage_count = (
        SELECT COUNT(*)
        FROM public.movie_tags mt
        WHERE mt.tag_id = public.tags.id
    );
END;
$$ LANGUAGE plpgsql;

-- ========================================
-- 5. 用户角色管理函数
-- ========================================

-- 检查是否为管理员
CREATE OR REPLACE FUNCTION is_admin(user_uuid UUID DEFAULT auth.uid())
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = user_uuid AND role = 'admin'
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 检查是否为版主
CREATE OR REPLACE FUNCTION is_moderator(user_uuid UUID DEFAULT auth.uid())
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = user_uuid AND (role = 'moderator' OR role = 'admin')
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 检查是否有特定角色
CREATE OR REPLACE FUNCTION has_role(
    user_uuid UUID, 
    required_role VARCHAR
)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM user_roles 
    WHERE user_id = user_uuid AND role = required_role
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取用户角色
CREATE OR REPLACE FUNCTION get_user_role(user_uuid UUID DEFAULT auth.uid())
RETURNS VARCHAR AS $$
DECLARE
  user_role VARCHAR(20);
BEGIN
  SELECT role INTO user_role
  FROM user_roles 
  WHERE user_id = user_uuid;
  
  RETURN COALESCE(user_role, 'user');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 设置用户角色
CREATE OR REPLACE FUNCTION set_user_role(
    target_user_id UUID,
    new_role VARCHAR,
    reason TEXT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
  current_role VARCHAR(20);
  affected_rows INTEGER;
BEGIN
  IF NOT is_admin() THEN
    RETURN json_build_object(
      'success', false,
      'error', '权限不足：只有管理员可以设置用户角色'
    );
  END IF;

  IF new_role NOT IN ('user', 'moderator', 'admin') THEN
    RETURN json_build_object(
      'success', false,
      'error', '无效的角色：必须是 user、moderator 或 admin'
    );
  END IF;

  SELECT role INTO current_role
  FROM user_roles 
  WHERE user_id = target_user_id;

  IF current_role = new_role THEN
    RETURN json_build_object(
      'success', true,
      'message', '用户角色已经是 ' || new_role
    );
  END IF;

  INSERT INTO public.user_roles (user_id, role, granted_by)
  VALUES (target_user_id, new_role, auth.uid())
  ON CONFLICT (user_id) 
  DO UPDATE SET 
    role = new_role,
    granted_by = auth.uid(),
    updated_at = NOW();

  GET DIAGNOSTICS affected_rows = ROW_COUNT;

  INSERT INTO public.role_change_logs (user_id, old_role, new_role, changed_by, reason)
  VALUES (target_user_id, current_role, new_role, auth.uid(), reason);

  RETURN json_build_object(
    'success', true,
    'message', '用户角色已更新为 ' || new_role,
    'old_role', current_role,
    'new_role', new_role
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 批量设置用户角色
CREATE OR REPLACE FUNCTION batch_set_user_roles(
    user_roles JSON,
    reason TEXT DEFAULT NULL
)
RETURNS JSON AS $$
DECLARE
  user_role JSON;
  result JSON;
  results JSON[] := '{}';
  success_count INTEGER := 0;
  error_count INTEGER := 0;
BEGIN
  IF NOT is_admin() THEN
    RETURN json_build_object(
      'success', false,
      'error', '权限不足：只有管理员可以批量设置用户角色'
    );
  END IF;

  FOR user_role IN SELECT * FROM json_array_elements(user_roles)
  LOOP
    result := set_user_role(
      (user_role->>'user_id')::UUID,
      user_role->>'role',
      reason
    );
    
    results := array_append(results, result);
    
    IF (result->>'success')::BOOLEAN THEN
      success_count := success_count + 1;
    ELSE
      error_count := error_count + 1;
    END IF;
  END LOOP;

  RETURN json_build_object(
    'success', true,
    'results', results,
    'summary', json_build_object(
      'total', json_array_length(user_roles),
      'success', success_count,
      'error', error_count
    )
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 获取所有用户及其角色
CREATE OR REPLACE FUNCTION get_all_users_with_roles()
RETURNS TABLE (
    user_id UUID,
    name VARCHAR,
    avatar_url TEXT,
    bio TEXT,
    is_ai BOOLEAN,
    followers INTEGER,
    uploads INTEGER,
    role VARCHAR
) AS $$
BEGIN
  IF NOT is_admin() THEN
    RAISE EXCEPTION '权限不足：只有管理员可以查看所有用户';
  END IF;

  RETURN QUERY
  SELECT 
    up.user_id,
    up.name,
    up.avatar_url,
    up.bio,
    up.is_ai,
    up.followers,
    up.uploads,
    COALESCE(ur.role, 'user') as role
  FROM user_profiles up
  LEFT JOIN user_roles ur ON up.user_id = ur.user_id
  ORDER BY up.name;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ========================================
-- 6. 触发器函数
-- ========================================

-- 评论回复通知触发器函数
CREATE OR REPLACE FUNCTION handle_comment_reply_notification()
RETURNS trigger AS $$
DECLARE
    parent_comment RECORD;
    movie_info RECORD;
    sender_profile RECORD;
BEGIN
    IF NEW.parent_id IS NOT NULL THEN
        SELECT 
            c.id, c.user_id, c.content, c.movie_id, up.name as user_name 
        INTO parent_comment 
        FROM public.comments c
        LEFT JOIN public.user_profiles up ON c.user_id = up.user_id
        WHERE c.id = NEW.parent_id;
        
        SELECT title INTO movie_info FROM public.movies WHERE id = NEW.movie_id;
        SELECT name INTO sender_profile FROM public.user_profiles WHERE user_id = NEW.user_id;
        
        PERFORM create_message(
            parent_comment.user_id,
            NEW.user_id,
            'comment_reply',
            '新回复',
            format('%s 回复了你在《%s》下的评论', 
                   COALESCE(sender_profile.name, '用户'), 
                   COALESCE(movie_info.title, '未知电影')),
            NEW.movie_id,
            NEW.id
        );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 电影上传通知触发器函数
CREATE OR REPLACE FUNCTION handle_movie_upload_notification()
RETURNS trigger AS $$
DECLARE
    uploader_profile RECORD;
BEGIN
    SELECT name INTO uploader_profile FROM public.user_profiles WHERE user_id = NEW.uploader_id;
    
    PERFORM create_message(
        NEW.uploader_id,
        NULL,
        'movie_upload',
        '电影上传成功',
        format('恭喜！你的电影《%s》已成功上传并发布。', NEW.title),
        NEW.id,
        NULL
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 标签使用计数更新触发器
CREATE OR REPLACE FUNCTION update_tag_usage_count_trigger()
RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE public.tags SET usage_count = usage_count + 1 WHERE id = NEW.tag_id;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE public.tags SET usage_count = usage_count - 1 WHERE id = OLD.tag_id;
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        IF OLD.tag_id != NEW.tag_id THEN
            UPDATE public.tags SET usage_count = usage_count + 1 WHERE id = NEW.tag_id;
            UPDATE public.tags SET usage_count = usage_count - 1 WHERE id = OLD.tag_id;
        END IF;
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 清理孤立投票记录的函数
CREATE OR REPLACE FUNCTION cleanup_orphaned_votes()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM public.comment_votes 
    WHERE comment_id NOT IN (SELECT id FROM public.comments);
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- ========================================
-- 7. 创建触发器
-- ========================================

-- 为表添加更新时间触发器
CREATE TRIGGER update_movies_updated_at 
    BEFORE UPDATE ON public.movies 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_profiles_updated_at 
    BEFORE UPDATE ON public.user_profiles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_comments_updated_at 
    BEFORE UPDATE ON public.comments 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_comment_votes_updated_at 
    BEFORE UPDATE ON public.comment_votes 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_messages_updated_at 
    BEFORE UPDATE ON public.messages 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_message_settings_updated_at 
    BEFORE UPDATE ON public.message_settings 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_roles_updated_at 
    BEFORE UPDATE ON public.user_roles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_tag_categories_updated_at 
    BEFORE UPDATE ON public.tag_categories 
    FOR EACH ROW EXECUTE FUNCTION update_tag_updated_at();

CREATE TRIGGER update_tags_updated_at 
    BEFORE UPDATE ON public.tags 
    FOR EACH ROW EXECUTE FUNCTION update_tag_updated_at();

-- 用户注册时自动创建资料的触发器
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

-- 确保消息设置存在的触发器
CREATE TRIGGER ensure_message_settings_trigger
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE ensure_message_settings();

-- 评论回复通知触发器
CREATE TRIGGER trigger_comment_reply_notification
    AFTER INSERT ON public.comments
    FOR EACH ROW
    EXECUTE FUNCTION handle_comment_reply_notification();

-- 电影上传通知触发器
CREATE TRIGGER trigger_movie_upload_notification
    AFTER INSERT ON public.movies
    FOR EACH ROW
    EXECUTE FUNCTION handle_movie_upload_notification();

-- 标签使用计数更新触发器
CREATE TRIGGER update_tag_usage_count_trigger
    AFTER INSERT OR UPDATE OR DELETE ON public.movie_tags
    FOR EACH ROW
    EXECUTE FUNCTION update_tag_usage_count_trigger();

-- ========================================
-- 使用说明
-- ========================================

/*
此脚本包含：

1. 基础触发器函数：
   - update_updated_at_column() - 更新时间字段
   - handle_new_user() - 用户注册时创建资料
   - ensure_message_settings() - 确保消息设置存在

2. 评论系统函数：
   - handle_comment_vote() - 处理评论投票
   - get_comments_with_replies() - 获取评论及回复
   - get_comment_replies() - 获取评论回复
   - create_comment_reply() - 创建回复

3. 消息系统函数：
   - create_message() - 创建消息
   - get_user_messages() - 获取用户消息
   - mark_message_as_read() - 标记消息已读
   - mark_all_messages_as_read() - 标记所有消息已读
   - get_unread_message_count() - 获取未读消息数量
   - delete_message() - 删除消息
   - update_message_settings() - 更新消息设置

4. 标签系统函数：
   - add_movie_tag() - 添加电影标签
   - add_movie_tags_batch() - 批量添加标签
   - remove_movie_tag() - 移除电影标签
   - remove_movie_tags_batch() - 批量移除标签
   - get_movie_tags() - 获取电影标签
   - get_movie_system_tags() - 获取电影系统标签
   - get_tags_by_category() - 按分类获取标签
   - set_movie_system_tags_simple() - 设置电影系统标签
   - sync_tag_usage_counts() - 同步标签使用计数

5. 用户角色管理函数：
   - is_admin() - 检查是否为管理员
   - is_moderator() - 检查是否为版主
   - has_role() - 检查是否有特定角色
   - get_user_role() - 获取用户角色
   - set_user_role() - 设置用户角色
   - batch_set_user_roles() - 批量设置用户角色
   - get_all_users_with_roles() - 获取所有用户及角色

6. 触发器函数：
   - handle_comment_reply_notification() - 评论回复通知
   - handle_movie_upload_notification() - 电影上传通知
   - update_tag_usage_count_trigger() - 标签使用计数更新
   - cleanup_orphaned_votes() - 清理孤立投票记录

7. 触发器：
   - 更新时间触发器
   - 用户注册触发器
   - 消息设置触发器
   - 评论回复通知触发器
   - 电影上传通知触发器
   - 标签使用计数触发器

注意事项：
- 所有函数都使用 SECURITY DEFINER 确保在正确的权限下执行
- 包含完整的业务逻辑和错误处理
- 支持批量操作和事务处理
- 包含完整的权限检查机制
*/
